package top.lbqaq.dao;

import top.lbqaq.pojo.Material;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author yzj
 * @Date 2021/11/22
 */
public class MaterialDao {

    public int getAllMaterialNumber(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        int count = 0;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select count(*) from material";
            rs = BaseDao.execute(connection, pstm, rs, sql);
            rs.next();
            count = rs.getInt(1);
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }

    public int getMaterialNumberByCategory(Connection connection,String mCategory) throws SQLException {
        PreparedStatement pstm = null;
        int count = 0;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            Object[] params={mCategory};
            String sql = "select count(*) from material where material_category=?";
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            rs.next();
            count = rs.getInt(1);
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }

    public int getMaterialNumberByName(Connection connection,String name) throws SQLException {
        PreparedStatement pstm = null;
        int count = 0;
        ResultSet rs = null;

        if (connection != null) {
            Object[] params={name};
            String sql = "select count(*) from material where material_name=?";
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            rs.next();
            count = rs.getInt(1);
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }

    public int getMaterialInNumberByName(Connection connection,String name) throws SQLException {
        PreparedStatement pstm = null;
        int count = 0;
        ResultSet rs = null;

        if (connection != null) {
            Object[] params={name};
            String sql = "select count(*) from material_in where material_name=?";
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            rs.next();
            count = rs.getInt(1);
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }

    public List<Material> getAllMaterial(Connection connection,int start,int rows) throws SQLException {
        Material material;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from material limit ?,?";
            Object[] params={start,rows};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material = new Material();
                material.setMaterialCategory(rs.getString("material_category"));
                material.setMaterialName(rs.getString("material_name"));
                material.setMaterialUnit(rs.getString("material_unit"));
                material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
                material.setMaterialAmount(rs.getLong("material_amount"));
                material.setMaterialUse(rs.getString("material_use"));
                material.setMaterialRemark(rs.getString("material_remark"));
                materials.add(material);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return materials;
    }


    public List<Material> getMaterialByCategory( Connection connection,int start,int rows,String mCategory) throws SQLException {
        Material material;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from material where material_category=? limit ?,?";
            Object[] params={mCategory,start,rows};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material = new Material();
                material.setMaterialCategory(rs.getString("material_category"));
                material.setMaterialName(rs.getString("material_name"));
                material.setMaterialUnit(rs.getString("material_unit"));
                material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
                material.setMaterialAmount(rs.getLong("material_amount"));
                material.setMaterialUse(rs.getString("material_use"));
                material.setMaterialRemark(rs.getString("material_remark"));
                materials.add(material);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return materials;
    }

    public Material getMaterialByName1(Connection connection, String name) throws SQLException{
        Material material = new Material();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null) {
            String sql = "select * from material where material_name=?";
            Object[] params = {name};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material.setMaterialCategory(rs.getString("material_category"));
                material.setMaterialName(rs.getString("material_name"));
                material.setMaterialUnit(rs.getString("material_unit"));
                material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
                material.setMaterialAmount(rs.getLong("material_amount"));
                material.setMaterialUse(rs.getString("material_use"));
                material.setMaterialRemark(rs.getString("material_remark"));
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return material;
    }

    public List<Material> getMaterialByName(Connection connection, int start,int rows,String mName) throws SQLException {
        Material material;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from material where material_name=? limit ?,?";
            Object[] params = {mName,start,rows};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material = new Material();
                material.setMaterialCategory(rs.getString("material_category"));
                material.setMaterialName(rs.getString("material_name"));
                material.setMaterialUnit(rs.getString("material_unit"));
                material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
                material.setMaterialAmount(rs.getLong("material_amount"));
                material.setMaterialUse(rs.getString("material_use"));
                material.setMaterialRemark(rs.getString("material_remark"));
                materials.add(material);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return materials;
    }

    public List<Material> getMaterialInByName(String mName, Connection connection) throws SQLException {
        Material material;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from material_in where material_name=?";
            Object[] params = {mName};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material = new Material();
              material.setMaterialCategory(rs.getString("material_category"));
              material.setMaterialName(rs.getString("material_name"));
              material.setMaterialInDate(rs.getString("material_in_date"));
              material.setMaterialInRemark(rs.getString("material_in_remark"));
              material.setMaterialInQuantity(rs.getString("material_in_quantity"));
              material.setMaterialInAmount(rs.getLong("material_in_amount"));
              material.setMaterialUnit(rs.getString("material_unit"));
              material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
              material.setSupplyCompany(rs.getString("supply_company"));
                materials.add(material);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return materials;
    }

    public List<Material> getMaterialInByName1(Connection connection, int start, int rows, String mName) throws SQLException {
        Material material;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Material> materials = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from material_in where material_name=? limit ? , ?";
            Object[] params = {mName,start,rows};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                material = new Material();
                material.setMaterialCategory(rs.getString("material_category"));
                material.setMaterialName(rs.getString("material_name"));
                material.setMaterialInDate(rs.getString("material_in_date"));
                material.setMaterialInRemark(rs.getString("material_in_remark"));
                material.setMaterialInQuantity(rs.getString("material_in_quantity"));
                material.setMaterialInAmount(rs.getLong("material_in_amount"));
                material.setMaterialUnit(rs.getString("material_unit"));
                material.setMaterialUnitPrice(rs.getLong("material_unit_price"));
                material.setSupplyCompany(rs.getString("supply_company"));
                materials.add(material);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return materials;
    }

    public int insertMaterial(Material a, Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        PreparedStatement pstm2 = null;
        ResultSet rs = null;
        int number=0;
        if (connection != null) {
               String sql2 ="insert into material values(?,?,?,?,?,?,?)";
               pstm2 = connection.prepareStatement(sql2);
               pstm2.setString(1,a.getMaterialCategory());
               pstm2.setString(2,a.getMaterialName());
               pstm2.setString(3,a.getMaterialUnit());
               pstm2.setLong(4,a.getMaterialUnitPrice());
               pstm2.setLong(5,a.getMaterialAmount());
               pstm2.setString(6,a.getMaterialUse());
               pstm2.setString(7,a.getMaterialRemark());
               number = pstm2.executeUpdate();
            BaseDao.closeResource(null, pstm, rs);
        }
        return number;
    }

    public int materialin(Material material, Connection connection) {
        PreparedStatement pstm = null;
        int rs=0;
        Object[] params = {material.getMaterialCategory(),material.getMaterialName(),material.getMaterialUnit(),material.getMaterialUnitPrice(),material.getMaterialInQuantity(),material.getMaterialInAmount(),material.getSupplyCompany(),material.getMaterialInDate(),material.getMaterialInRemark()};
        if (connection != null) {
            String sql ="insert into material_in values(?,?,?,?,?,?,?,?,?)";
            try {
                rs = BaseDao.execute(connection,pstm,sql,params);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        BaseDao.closeResource(null, pstm, null);
        return rs;
    }

    public int updateByName(Material material,Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        Object[] params = {material.getMaterialCategory(),material.getMaterialUnit(),material.getMaterialUnitPrice(),material.getMaterialAmount(),material.getMaterialUse(),material.getMaterialRemark(),material.getMaterialName()};
        int rs = 0;
        if (connection != null) {
            String sql = "update material set material_category=?,material_unit=?,material_unit_price=?,material_amount=?,material_use=?,material_remark=? where material_name=?";
            rs=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return rs;
    }

    public int deleteByName(String name,Connection connection){
        PreparedStatement pstm = null;
        Object[] params = {name};
        int rs = 0;
        if (connection != null) {
            String sql = "delete from material where material_name=?";
            try {
                rs=BaseDao.execute(connection, pstm, sql, params);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            BaseDao.closeResource(null, pstm, null);
        }
        return rs;
    }
}
